package com.css.fxfzcpxx.modules.logRecord.repository.impl;

import com.css.fxfzcpxx.modules.logRecord.entity.SysAccessLog;
import com.css.fxfzcpxx.modules.logRecord.entity.SysLog;
import com.css.fxfzcpxx.modules.logRecord.repository.AccessLogRecordRepository;
import com.css.fxfzcpxx.modules.systemOperation.vo.StatisticsDataVo;
import com.css.fxfzcpxx.util.PlatformObjectUtils;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.namedparam.BeanPropertySqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.stereotype.Repository;

import javax.annotation.Resource;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * @Author:
 * @CreateTime: 2023/9/15 10:33
 * @Description:
 */
@Repository
public class AccessLogRecordRepositoryImpl implements AccessLogRecordRepository {
    @Resource
    JdbcTemplate jdbcTemplate;

    @Resource
    NamedParameterJdbcTemplate namedParameterJdbcTemplate;
    @Override
    public SysAccessLog findByClientIp(String clientIp) {
        String sql="SELECT * FROM sys_access_log  WHERE op_time::date = current_date and client_ip=:clientIp";
        Map<String,String> idMap = new HashMap();
        idMap.put("clientIp", clientIp);
        try {
            return namedParameterJdbcTemplate.queryForObject(sql, idMap, new BeanPropertyRowMapper<>(SysAccessLog.class));
        }catch (Exception exception){
            return null;
        }
    }

    @Override
    public void save(SysAccessLog sysAccessLog) {
        String sql = "insert into sys_access_log(log_id,client_ip,page_view,op_time,op_user) " +
                "values(:logId, :clientIp, :pageView, :opTime, :opUser)";
        SqlParameterSource source = new BeanPropertySqlParameterSource(sysAccessLog);
        namedParameterJdbcTemplate.update(sql, source);
    }

    @Override
    public Integer getTotalPageView() {
        String sql = "select sum(page_view) from sys_access_log ";
        Integer total = jdbcTemplate.queryForObject(sql, Integer.class);
        if(PlatformObjectUtils.isEmpty(total)){
            return 0;
        }
        return total;
    }

    @Override
    public List<StatisticsDataVo> getVisitsStatisticsData(String unit) {
        String sql = "";
        if("year".equals(unit)) {
             sql = "WITH months AS ( " +
                     "    SELECT" +
                     "        generate_series (" +
                     "            date_trunc('month', CURRENT_DATE) - INTERVAL '11 months'," +
                     "            date_trunc('month', CURRENT_DATE)," +
                     "            INTERVAL '1 month' " +
                     "        ) AS month_start " +
                     ") " +
                     "SELECT " +
                     "    to_char(months.month_start, 'YYYY-MM') AS unit, " +
                     "    COALESCE(SUM(sys_access_log.page_view), 0) AS pageview " +
                     "FROM " +
                     "    months " +
                     "LEFT JOIN sys_access_log ON  " +
                     "    EXTRACT(YEAR FROM sys_access_log.op_time) = EXTRACT(YEAR FROM months.month_start)  " +
                     "    AND EXTRACT(MONTH FROM sys_access_log.op_time) = EXTRACT(MONTH FROM months.month_start) " +
                     "GROUP BY " +
                     "    months.month_start " +
                     "ORDER BY " +
                     "    months.month_start;";
        }else{
            sql ="WITH DateSeries AS (" +
                    "    SELECT " +
                    "        generate_series (" +
                    "            DATE_TRUNC('day', CURRENT_DATE - INTERVAL '29 days'), " +
                    "            DATE_TRUNC('day', CURRENT_DATE)," +
                    "            '1 day'::INTERVAL\n" +
                    "        ) AS DAY" +
                    ")" +
                    "SELECT" +
                    "    TO_CHAR(ds.DAY, 'YYYY-MM-DD') AS unit," +
                    "    COALESCE(SUM(sl.page_view), 0) AS pageView " +
                    "FROM" +
                    "    DateSeries ds\n" +
                    "LEFT JOIN sys_access_log sl ON DATE_TRUNC('day', sl.op_time) = ds.DAY " +
                    "GROUP BY" +
                    "    ds.DAY " +
                    "ORDER BY " +
                    "    ds.DAY;";
        }
        List<StatisticsDataVo> list = jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(StatisticsDataVo.class));

        return list;
    }

}
